////////////////////////////////////////////////////////////////////////////////
// Copyright 2009 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   XLS
// AUTHOR           :   Saint Peng 
// DATE CREATE      :   2009/05/13
// DESCRIPTION      :   a DAO file for the function, XLS
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2009/05/13   Saint Peng      1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////

package com.erp.app.common.xls.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.erp.app.common.ProjectConstants;
import com.erp.app.common.xls.criteria.XlsHdrSrchCriteria;
import com.erp.app.common.xls.pk.XlsHdrPk;
import com.erp.app.common.xls.pk.XlsSqlPk;
import com.erp.app.common.xls.pk.XlsCellPk;
import com.erp.app.common.xls.vo.XlsHdrVo;
import com.erp.app.common.xls.vo.XlsSqlVo;
import com.erp.app.common.xls.vo.XlsCellVo;

import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.criteria.SimpleSearchMaintStyleSrchCriteria;
import com.erp.framework.model.dao.SimpleSearchMaintStyleDao;
import com.erp.framework.model.pk.SimpleBasePk;
import com.erp.framework.model.pk.SimpleSearchMaintStylePk;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.model.vo.SimpleSearchMaintStyleVo;
import com.erp.framework.types.CustomBigDecimal;
import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.types.CustomSqlDate;
import com.erp.framework.types.CustomTimestamp;
import com.erp.framework.types.CustomUtilDate;
import com.erp.framework.util.DateTimeUtil;
import com.erp.framework.util.NumberUtil;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;

/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Saint Peng 
* @version 1.0     2009/05/13
* @since           FrameWork 1.0
*
*/
public class XlsSqlDao extends SimpleSearchMaintStyleDao {

	/**
	* Returns an array list of child VOs by the parent
	* @param         parent   the incoming parent VO
	* @param         conn     the database connection
	* @return        an array list of child VOs
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
		throws FindRecordException {

		return super.selectByParent(parent, XlsSqlVo.class, conn);
	}

	/**
	* Constructs a string of SQL insert statement
	* @return        a string of SQL insert statement
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected String sqlInsert() {

		String sql = "INSERT INTO T_XLS_SQL ("
			+ "COMPANY_CODE, MENU_ID, FUN_ID, LINE_NO, XLS_SQL, "
			+ "IS_MAIN, SQL_TYPE, LINE, HAS_REPEAT_LBL, REPEAT_LINE, REPEAT_FLAG, " +
					"S_LINE1, S_LINE1_FLAG, S_LINE2, S_LINE2_FLAG, S_LINE3, S_LINE3_FLAG, S_LINE4, S_LINE4_FLAG, S_LINE5, S_LINE5_FLAG, " +
					"REPEAT_LINE_TO) "
			+ "VALUES ("
			+ "?, ?, ?, ?, ?, "
			+ "?, ?,?, ?, ?, ?, " +
					"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
					"? )";
		return sql;
	}

	/**
	* Sets the values for a SQL insert or update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
		throws SQLException {

		XlsSqlVo vo = (XlsSqlVo) obj;
		int i = 1;

		pStmt.setString(i++, vo.getCompanyCode());
		pStmt.setString(i++, vo.getMenuId());
		pStmt.setString(i++, vo.getFunId());
		pStmt.setString(i++, vo.getSqlLineNo());
		setNullableString(pStmt, i++, vo.getSqlXlsSql());
		setNullableString(pStmt, i++, vo.getSqlIsMain());
		setNullableString(pStmt, i++, vo.getSqlSqlType());
		setNullableBigInteger(pStmt, i++, vo.getSqlLine());
		setNullableString(pStmt, i++,vo.getSqlHasRepeatLbl());
		setNullableBigInteger(pStmt, i++, vo.getSqlRepeatLine());
		setNullableString(pStmt, i++, vo.getSqlRepeatFlag());
		
		setNullableBigInteger(pStmt, i++, vo.getSqlSLine1());
		setNullableString(pStmt, i++, vo.getSqlSLine1Flag());
		setNullableBigInteger(pStmt, i++, vo.getSqlSLine2());
		setNullableString(pStmt, i++, vo.getSqlSLine2Flag());
		setNullableBigInteger(pStmt, i++, vo.getSqlSLine3());
		setNullableString(pStmt, i++, vo.getSqlSLine3Flag());
		setNullableBigInteger(pStmt, i++, vo.getSqlSLine4());
		setNullableString(pStmt, i++, vo.getSqlSLine4Flag());
		setNullableBigInteger(pStmt, i++, vo.getSqlSLine5());
		setNullableString(pStmt, i++, vo.getSqlSLine5Flag());
		setNullableBigInteger(pStmt, i++, vo.getSqlRepeatLineTo());
		
		if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
			pStmt.setString(i++, vo.getCompanyCode());
			pStmt.setString(i++, vo.getMenuId());
			pStmt.setString(i++, vo.getFunId());
			pStmt.setString(i++, vo.getSqlLineNo());
			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
		}
	}

	/**
	* Sets the values for a SQL insert statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
	}

	/**
	* Constructs a string of SQL update statement
	* @return        a string of SQL update statement
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected String sqlUpdate() {

		String sql = "UPDATE T_XLS_SQL SET COMPANY_CODE=?, MENU_ID=?, FUN_ID=?, LINE_NO=?, XLS_SQL=?, "
			+ "IS_MAIN=?, SQL_TYPE=? , LINE=? , HAS_REPEAT_LBL=?, REPEAT_LINE=?, REPEAT_FLAG=?, " +
					" S_LINE1=?, S_LINE1_FLAG=?, S_LINE2=?, S_LINE2_FLAG=?, S_LINE3=?, S_LINE3_FLAG=?, S_LINE4=?, S_LINE4_FLAG=?, S_LINE5=?, S_LINE5_FLAG=?, " +
					"REPEAT_LINE_TO=? "
			+ "WHERE COMPANY_CODE=? AND MENU_ID=? AND FUN_ID=? AND LINE_NO=? "
			+ "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";
		return sql;
	}

	/**
	* Sets the values for a SQL update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected void setValuesForModify(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
	}

	/**
	* Constructs a string of SQL delete statement
	* @return        a string of SQL update statement
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected String sqlDelete() {
		String sql = "DELETE FROM T_XLS_SQL WHERE COMPANY_CODE=? AND MENU_ID=? AND FUN_ID=? AND LINE_NO=? ";

		return sql;
	}

	/**
	* Sets the values for a SQL delete statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
		throws SQLException {

		XlsSqlVo vo = (XlsSqlVo) obj;
		int i = 1;

		pStmt.setString(i++, vo.getCompanyCode());
		pStmt.setString(i++, vo.getMenuId());
		pStmt.setString(i++, vo.getFunId());
		pStmt.setString(i++, vo.getSqlLineNo());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected String sqlFind() {

		String sql = "SELECT COMPANY_CODE, MENU_ID, FUN_ID, LINE_NO, XLS_SQL, "
			+ "IS_MAIN, SQL_TYPE , LINE, HAS_REPEAT_LBL, REPEAT_LINE, REPEAT_FLAG, " +
					"S_LINE1, S_LINE1_FLAG, S_LINE2, S_LINE2_FLAG, S_LINE3, S_LINE3_FLAG, S_LINE4, S_LINE4_FLAG, S_LINE5, S_LINE5_FLAG, " +
					"REPEAT_LINE_TO "
			+ "FROM T_XLS_SQL "
			+ "WHERE COMPANY_CODE=? AND MENU_ID=? AND FUN_ID=? AND LINE_NO=? "
			+ "ORDER BY COMPANY_CODE, MENU_ID, FUN_ID, LINE_NO";
		return sql;
	}

	/**
	* Sets the values for a SQL select statement for finding by primary key.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming PK
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected void setValuesForFind(PreparedStatement pStmt, Object obj)
		throws SQLException {

		XlsSqlPk pk = (XlsSqlPk) obj;
		int i = 1;

		pStmt.setString(i++, pk.getCompanyCode());
		pStmt.setString(i++, pk.getMenuId());
		pStmt.setString(i++, pk.getFunId());
		pStmt.setString(i++, pk.getSqlLineNo());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected String sqlList() {

		String sql = "SELECT COMPANY_CODE, MENU_ID, FUN_ID, LINE_NO, XLS_SQL, "
			+ "IS_MAIN, SQL_TYPE , LINE, HAS_REPEAT_LBL, REPEAT_LINE, REPEAT_FLAG," +
					"S_LINE1, S_LINE1_FLAG, S_LINE2, S_LINE2_FLAG, S_LINE3, S_LINE3_FLAG, S_LINE4, S_LINE4_FLAG, S_LINE5, S_LINE5_FLAG," +
					"REPEAT_LINE_TO "
			+ "FROM T_XLS_SQL "
			+ "WHERE COMPANY_CODE=? AND MENU_ID=? AND FUN_ID=? "
			+ "ORDER BY COMPANY_CODE, MENU_ID, FUN_ID, LINE_NO";
		return sql;
	}

	/**
	* Sets the values for a SQL select statement for finding child tables.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming parent PK
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected void setValuesForList(PreparedStatement pStmt, Object obj)
		throws SQLException {

		XlsHdrPk pk = (XlsHdrPk) obj;
		int i = 1;

		pStmt.setString(i++, pk.getCompanyCode());
		pStmt.setString(i++, pk.getMenuId());
		pStmt.setString(i++, pk.getFunId());
	}

	/**
	* Sets the values of a result set to a vo
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Saint Peng 
	* @since      Framework 1.0
	*/
	protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {

		XlsSqlVo vo = new XlsSqlVo();

		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setMenuId(rs.getString("MENU_ID"));
		vo.setFunId(rs.getString("FUN_ID"));
		vo.setSqlLineNo(rs.getString("LINE_NO"));
		vo.setSqlXlsSql(rs.getString("XLS_SQL"));
		vo.setSqlIsMain(rs.getString("IS_MAIN"));
		vo.setSqlSqlType(rs.getString("SQL_TYPE"));
		vo.setSqlLine(CustomBigInteger.customize(rs.getBigDecimal("LINE")));
		vo.setSqlHasRepeatLbl(rs.getString("HAS_REPEAT_LBL"));
		vo.setSqlRepeatLine(CustomBigInteger.customize(rs.getBigDecimal("REPEAT_LINE")));
		vo.setSqlRepeatFlag(rs.getString("REPEAT_FLAG"));
		
		vo.setSqlSLine1(CustomBigInteger.customize(rs.getBigDecimal("S_LINE1")));
		vo.setSqlSLine1Flag(rs.getString("S_LINE1_FLAG"));
		vo.setSqlSLine2(CustomBigInteger.customize(rs.getBigDecimal("S_LINE2")));
		vo.setSqlSLine2Flag(rs.getString("S_LINE2_FLAG"));
		vo.setSqlSLine3(CustomBigInteger.customize(rs.getBigDecimal("S_LINE3")));
		vo.setSqlSLine3Flag(rs.getString("S_LINE3_FLAG"));
		vo.setSqlSLine4(CustomBigInteger.customize(rs.getBigDecimal("S_LINE4")));
		vo.setSqlSLine4Flag(rs.getString("S_LINE4_FLAG"));
		vo.setSqlSLine5(CustomBigInteger.customize(rs.getBigDecimal("S_LINE5")));
		vo.setSqlSLine5Flag(rs.getString("S_LINE5_FLAG"));
		vo.setSqlRepeatLineTo(CustomBigInteger.customize(rs.getBigDecimal("REPEAT_LINE_TO")));
		
		return vo;
	}

}
